/* InOutStatisticSearchClass.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author Date       Description
 *   ---------- ---------- ---------------------------------------------------
 *   Administrator  2008-3-25   First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.base;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import framework.core.Conditions;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBResult;
import framework.core.ObjectList;
import framework.core.UniversalRow;
import framework.core.UniversalTable;
import framework.core.Value;

public class InOutStatisticSearchClass {    
    /** 特定条件查询入库数量*/
    public Vector getInSum(DBConnection con,ObjectList mReturn,InOutStatisticSearchEvent event){
        Vector vec = new Vector();
        for (int i = 0; i < mReturn.size(); i++) {
            FirstForm f1 = new FirstForm();
            DBResult dbrs = null;
            ResultSet rs = null;  
            UniversalRow row = null;
            try {
                row = (UniversalRow) mReturn.get(i);
            } catch (Exception e) {
                
                e.printStackTrace();
            }
            String in = "select " +
            		"sum(b.PRODUCT_COUNT) " +
            		"from t_in_head a,t_in_sub b ";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                in += " ,t_provider f ";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                in += " ,t_product e ";
            }
            in +="where a.IN_BILL_CODE=b.IN_BILL_CODE and b.status = '" + InSub.STATUS_AVALIBLE + "'";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                in += "and a.PROVIDER_CODE = f.CODE ";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                in += " and upper(b.PRODUCT_CODE) = e.CONCENTRICITY_CODE  ";
            }
            in += "and a.warehouse_code = '"+row.getString(1)+"' " +
            				"and b.POSITION_CODE = '"+row.getString(2)+"' " +
            						"and upper(b.PRODUCT_CODE) = upper('"+row.getString(3)+"') " +
            								"and b.PRODUCT_PRICE = '"+row.getString(4)+"'";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                in += " and f.name = '" + event.getProviderName() + "'";
            }
            if (!event.getBalanceFlag().equalsIgnoreCase("")) {
                in += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
            }
            if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
                in += " and a.in_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
            }
            if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
                in += " and a.in_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                in += " and e.is_normal = '" + event.getIsNormal() + "'";
            }
            if (!event.getBalanceMode().equalsIgnoreCase("")) {
                in += " and b.balance_mode = '" + event.getBalanceMode() + "'";
            }
            if (event.getWarehouseCodeUser().length() > 0) {
                in += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
            }
            if(event.getProductCode().length() > 0){
                in += " and upper(b.PRODUCT_CODE) like '%"+event.getProductCode().toUpperCase()+"%'";
            }
            try {
                dbrs = DBEngine.doSelect(con, in);
                rs = dbrs.getRs();
                int nums = 0;
                while (rs.next()) {
                    f1._warehouse_code = row.getString(1);
                    f1._position_code = row.getString(2);
                    f1._product_code = row.getString(3);
                    f1.chanpinbianma = row.getString(3);
                    f1._product_price = row.getString(4);
                    f1.rukudanjia = row.getString(4);
                    f1.chukudanjia = row.getString(4);
                    if(rs.getString(1) == null || rs.getString(1).equals("")){
                        f1.rukushulaing = String.valueOf(Value.parseDouble(f1.rukushulaing,0)+ Double.parseDouble("0"));
                    }else{
                        f1.rukushulaing = String.valueOf(Value.parseDouble(f1.rukushulaing,0)+ Double.parseDouble(rs.getString(1)));
                    }
                }
            } catch (SQLException e) {
                
                e.printStackTrace();
            } catch (Exception e) {
                
                e.printStackTrace();
            } finally {
                try {
                    dbrs.clear();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
            vec.add(f1);
        }
        return vec;
    }
    
    /** 特定条件查询出库数量*/
    public Vector getOutSum(DBConnection con,Vector mReturn,InOutStatisticSearchEvent event){
        Vector vec = mReturn;
        for (int i = 0; i < mReturn.size(); i++) {
            FirstForm f1 = (FirstForm)mReturn.get(i);
            DBResult dbrs = null;
            ResultSet rs = null;  
            String out = "select " +
            		"sum(d.PRODUCT_COUNT) " +
            		"from t_out_head c,t_out_sub d ";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                out += " ,t_provider f ";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                out += " ,t_product e ";
            }
            out +=  "where c.out_BILL_CODE=d.out_BILL_CODE and d.status = '" + OutSub.STATUS_AVALIBLE + "'";
//            if (!event.getProviderName().equalsIgnoreCase("")&&!event.getIsNormal().equalsIgnoreCase("")) {
//                out += "and e.PROVIDER_CODE = f.CODE ";
//            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                out += " and upper(d.PRODUCT_CODE) = e.CONCENTRICITY_CODE  ";
            }
            out += "and c.warehouse_code = '"+f1._warehouse_code+"' " +
            				"and d.POSITION_CODE = '"+f1._position_code+"' " +
            						"and upper(d.PRODUCT_CODE) = upper('"+f1._product_code+"') " +
            								"and d.PRODUCT_PRICE = '"+f1._product_price+"'";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                out += " and f.name = '" + event.getProviderName() + "'";
            }
            if (!event.getBalanceFlag().equalsIgnoreCase("")) {
                out += " and c.balance_flag = '" + event.getBalanceFlag() + "'";
            }
            if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
                out += " and c.out_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
            }
            if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
                out += " and c.out_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                out += " and e.is_normal = '" + event.getIsNormal() + "'";
            }
            if (!event.getBalanceMode().equalsIgnoreCase("")) {
                out += " and d.balance_mode = '" + event.getBalanceMode() + "'";
            }
            if (event.getWarehouseCodeUser().length() > 0) {
                out += " and c.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
            }
            if(event.getProductCode().length() > 0){
                out += " and upper(d.PRODUCT_CODE) like '%"+event.getProductCode().toUpperCase()+"%'";
            }
            try {
                dbrs = DBEngine.doSelect(con, out);
                rs = dbrs.getRs();
                if (rs.next()) {
                    f1.chukushuliang = rs.getString(1);
                }
            } catch (SQLException e) {
                
                e.printStackTrace();
            } catch (Exception e) {
                
                e.printStackTrace();
            } finally {
                try {
                    dbrs.clear();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
            vec.set(i, f1);
        }
        return vec;
    }
    
    /**填充查询*/
    public Vector searchFull(DBConnection con,Vector mReturn,InOutStatisticSearchEvent event){
        Vector vec = new Vector();
        vec = mReturn;
        for (int i = 0; i < vec.size(); i++) {
            FirstForm f1 = (FirstForm)vec.get(i);
            DBResult dbrs = null;
            ResultSet rs = null;  
            String sql = "select " +
            		"a.warehouse_name warehouse_name,b.POSITION_NAME POSITION_NAME,e.name product_name, e.product_unit unit, e.spec spec, e.model model, e.product_brand product_brand, f.name name " +
            		"from t_in_head a,t_in_sub b,t_product e,t_provider f " +
            		"where a.IN_BILL_CODE=b.IN_BILL_CODE " +
            		"and b.status = '" + InSub.STATUS_AVALIBLE + "'" +
             		"and upper(b.PRODUCT_CODE) = e.CONCENTRICITY_CODE " +
            		"and a.PROVIDER_CODE = f.CODE " +
            		"and a.warehouse_code = '"+f1._warehouse_code+"' " +
            				"and b.POSITION_CODE = '"+f1._position_code+"' " +
            						"and upper(b.PRODUCT_CODE) = upper('"+f1._product_code+"') " +
            								"and b.PRODUCT_PRICE = '"+f1._product_price+"'";
            if (!event.getProviderName().equalsIgnoreCase("")) {
                sql += " and f.name = '" + event.getProviderName() + "'";
            }
//            if (!event.getBalanceFlag().equalsIgnoreCase("")) {
//                sql += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
//            }

//            if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
//                sql += " and a.out_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
//            }
//            if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
//                sql += " and a.out_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
//            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                sql += " and e.is_normal = '" + event.getIsNormal() + "'";
            }
            if (!event.getBalanceMode().equalsIgnoreCase("")) {
                sql += " and b.balance_mode = '" + event.getBalanceMode() + "'";
            }
            if (event.getWarehouseCodeUser().length() > 0) {
                sql += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
            }
            try {
                dbrs = DBEngine.doSelect(con, sql);
                rs = dbrs.getRs();
                if (rs.next()) {
                    f1.cangku = rs.getString(1);
                    f1.huowei = rs.getString(2);
                    f1.chanpinmingcheng = rs.getString(3);
                    f1.danwei = rs.getString(4);
                    f1.guige = rs.getString(5);
                    f1.xinghao = rs.getString(6);
                    f1.pinpai = rs.getString(7);
                    f1.gongyingshang = rs.getString(8);
                }
            } catch (SQLException e) {
                
                e.printStackTrace();
            } catch (Exception e) {
                
                e.printStackTrace();
            } finally {
                try {
                    dbrs.clear();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
            vec.set(i, f1);
        }
        return vec;
    }
    
    /**各种数据计算*/
    public Vector operation(Vector mList) {
        Vector vecFianl = new Vector();
        vecFianl = mList;
        for (int i = 0; i < vecFianl.size(); i++) {
            FirstForm f1 = (FirstForm) vecFianl.get(i);
            f1.kucunshuliang = String.valueOf(Double.parseDouble(f1.rukushulaing) - Double.parseDouble(f1.chukushuliang));
            f1.kucunchengben =String.valueOf(Double.parseDouble(f1.rukudanjia)*Double.parseDouble(f1.kucunshuliang));
            f1.rukujine = String.valueOf(Double.parseDouble(f1.rukudanjia)*Double.parseDouble(f1.rukushulaing));
            f1.chukuchengben = String.valueOf(Double.parseDouble(f1.chukudanjia)*Double.parseDouble(f1.chukushuliang));
            vecFianl.set(i, f1);
        }
        return vecFianl;
    }
    
    /**空值判断*/
    public Vector checkNull(Vector mList){
        Vector vec = new Vector();
        vec = mList;
        for (int i = 0; i < vec.size(); i++) {
            FirstForm f = (FirstForm)vec.get(i);
            if(f.cangku == null||f.cangku.equals("")){
                f.cangku = "-0";
            }
            if(f.huowei == null||f.huowei.equals("")){
                f.huowei = "-0";
            }
            if(f.chanpinbianma == null||f.chanpinbianma.equals("")){
                f.chanpinbianma = "-0";
            }
            if(f.chanpinmingcheng == null||f.chanpinmingcheng.equals("")){
                f.chanpinmingcheng = "-0";
            }
            if(f.danwei == null||f.danwei.equals("")){
                f.danwei = "-0";
            }
            if(f.guige == null||f.guige.equals("")){
                f.guige = "-0";
            }
            if(f.xinghao == null||f.xinghao.equals("")){
                f.xinghao = "-0";
            }
            if(f.pinpai == null||f.pinpai.equals("")){
                f.pinpai = "-0";
            }
            if(f.gongyingshang == null||f.gongyingshang.equals("")){
                f.gongyingshang = "-0";
            }
            if(f.rukushulaing == null||f.rukushulaing.equals("")){
                f.rukushulaing = "-0";
            }
            if(f.rukujine == null||f.rukujine.equals("")){
                f.rukujine = "-0";
            }
            if(f.chukushuliang == null||f.chukushuliang.equals("")){
                f.chukushuliang = "-0";
            }
            if(f.chukuchengben == null||f.chukuchengben.equals("")){
                f.chukuchengben = "-0";
            }
            if(f.kucunshuliang == null||f.kucunshuliang.equals("")){
                f.kucunshuliang = "-0";
            }
            if(f.kucunchengben == null||f.kucunchengben.equals("")){
                f.kucunchengben = "-0";
            }
            if(f.rukudanjia == null||f.rukudanjia.equals("")){
                f.rukudanjia = "-0";
            }
            if(f.chukudanjia == null||f.chukudanjia.equals("")){
                f.chukudanjia = "-0";
            }
            vec.set(i, f);
        }
        return vec;
    }
    /**方法合并*/
    public Vector setAll(DBConnection con,ObjectList mReturn,InOutStatisticSearchEvent event){
        //如果按条件查询出的产品结果为空   直接跳过后续查询
        if(mReturn == null || mReturn.size() == 0){
            return null;
        }
        Vector v1 = getInSum(con, mReturn, event);
        Vector v2 = getOutSum(con, v1, event);
        Vector v3 = searchFull(con, v2, event);
        Vector v4 = checkNull(v3);
        Vector v5 = operation(v4);
//        /**测试*/
//        Vector operMoney = this.full(con, event);
//        FirstForm obj =(FirstForm) this.operMoneyMethod(operMoney);
//        this.print(operMoney);
//        /**测试*/
//        System.out.println("**********************************************");
//        System.out.println("**********************************************");
//        System.out.println("**********************************************");
//        this.print(v5);
//        System.out.println("**********************************************");
//        System.out.println("**********************************************");
//        System.out.println("**********************************************");
//        v5.add(obj);
        return v5;
    }
    
    /**金额计算*/
    public Object operMoneyMethod(Vector mList){
        Vector vec = new Vector();
        double inMoney = 0;
        double outMoney = 0;
        double nowMoney = 0;
        vec = mList;
        for (int i = 0; i < vec.size(); i++) {
            FirstForm f1 = (FirstForm) vec.get(i);
            inMoney += Double.parseDouble(f1.rukujine);
            outMoney += Double.parseDouble(f1.chukuchengben);
            nowMoney += Double.parseDouble(f1.kucunchengben);
        }
        FirstForm f2 = new FirstForm();
        f2.inMoney = inMoney;
        f2.outMoney = outMoney;
        f2.nowMoney = nowMoney;
        return f2;
    }
    
    /**查询所有金额 (以每条记录单独查询)   (作废)*/
    public FirstForm selectMoney(String sql,DBConnection con,InOutStatisticSearchEvent event){
        Vector v1 = new Vector();
        DBResult dbrs = null;
        ResultSet row = null;  
        try {
            dbrs = DBEngine.doSelect(con, sql);
            row = dbrs.getRs();
            while (row.next()) {
                FirstForm f1 = new FirstForm();
                f1._warehouse_code = row.getString(1);
                f1._position_code = row.getString(2);
                f1._product_code = row.getString(3);
                f1._product_price = row.getString(4);
                v1.add(f1);
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                dbrs.clear();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }
        Vector v2 = getInSum(con, v1, event);
        Vector v3 = getOutSum(con, v2, event);
        Vector v4 = checkNull(v3);
        Vector v5 = operation(v4);
        FirstForm obj = (FirstForm)operMoneyMethod(v5);
        System.out.println(obj.inMoney+"-----------------------------");
        System.out.println(obj.outMoney+"-----------------------------");
        System.out.println(obj.nowMoney+"-----------------------------");
        return obj;
    }
    
    /**查询所有金额 (直接查询)*/
    public FirstForm selectMoney(DBConnection con,InOutStatisticSearchEvent event){
        String[] warehouse_code = event.getWarehouseCodes();
        String provider_name = event.getProviderName();
        String start = event.getStartStatisticTime();
        String end = event.getEndStatisticTime();
        String balance_mode = event.getBalanceMode();
        String balance_flag = event.getBalanceFlag();
        String concentricity_code = event.getProductCode();
        String is_normal = event.getIsNormal();
        String in = "";
        String out = "";
        
        String warehouseCodes = "";
        
        
        for (int i = 0; i < warehouse_code.length; i++) {
            if(warehouse_code.length == 1){
                warehouseCodes = "'"+warehouse_code[i]+"'";
                break;
            }
            if(i == warehouse_code.length - 1 && warehouse_code.length != 1){
                warehouseCodes = "'"+warehouse_code[i]+"'";
                break;
            }
            warehouseCodes = "'"+warehouse_code[i]+"',";
        }
        in = " select "+
        " sum(insub.product_money) in_moneys "+  
        " from "+  
        " t_in_sub insub "+  
        " left outer join   t_in_head inhead "+   
        " on   insub.in_bill_id = inhead.id "+  
        " left outer join   t_product pro "+   
        " on   insub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = inhead.provider_code "+  
        " where "+  
        " 1 = 1 and insub.status = '1'"+  
            " and  "+
            " inhead.warehouse_code  in ("+warehouseCodes+") ";
        if(provider_name != "")
            in += " and inhead.provider_code in (SELECT code  FROM t_provider WHERE NAME LIKE '%"+provider_name+"%' )"; 
        if(start != "")
            in += " and inhead.in_time >= '"+start+"' ";
        if(end != ""){
            if(end.length() <= 10) end += " 23:59:59";
            in += " and inhead.in_time <= '"+end+"' ";
        }    
        if(balance_mode != "")
            in += " and insub.balance_mode = '"+balance_mode+"' ";
        if(balance_flag != "")
            in += " and inhead.balance_flag = '"+balance_flag+"' ";
        if(concentricity_code != "")
            in += " and pro.concentricity_code = '"+concentricity_code+"' ";
        if(is_normal != "")
            in += " and pro.is_normal = '"+is_normal+"' ";
    out = " select "+  
    " sum(outsub.product_money) out_moneys "+  
    " from  t_out_sub outsub "+  
    " left outer join   t_out_head outhead "+   
    " on   outsub.out_bill_code = outhead.out_bill_code "+  
    " left outer join   t_product pro "+   
    " on   outsub.product_id = pro.id "+  
    " left outer join   t_provider per "+   
    " on   per.code = pro.provider_code "+  
    " where "+  
    " 1 = 1 and outsub.status = '1'"+  
        " and  "+
        " outhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
    	out += " and outsub.provider_code in (SELECT code  FROM t_provider WHERE NAME LIKE '%"+provider_name+"%' )"; 
    if(start != "")
        out += " and outhead.out_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        out += " and outhead.out_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        out += " and outsub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        out += " and outhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        out += " and pro.concentricity_code = '"+concentricity_code+"' ";
    if(is_normal != "")
        out += " and pro.is_normal = '"+is_normal+"' ";   
        
        DBResult dbrsIn = null;
        DBResult dbrsOut = null;
        ResultSet rowIn = null;
        ResultSet rowOut = null;
        FirstForm f1 = new FirstForm();
        try {
            dbrsIn = DBEngine.doSelect(con, in);
            dbrsOut = DBEngine.doSelect(con, out);
            rowIn = dbrsIn.getRs();
            rowOut = dbrsOut.getRs();
            if (rowIn.next()) {
                String str1 = rowIn.getString(1);
                if(str1 != null && !str1.equals("")){
                    f1.inMoney = Double.parseDouble(str1);
                }
            }
            if (rowOut.next()) {
                String str2 = rowOut.getString(1);
                if(str2 != null && !str2.equals("")){
                    f1.outMoney = Double.parseDouble(str2);
                }
            }
            f1.nowMoney = f1.inMoney - f1.outMoney;
        } catch (SQLException e) {
            
            e.printStackTrace();
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                rowIn.close();
                rowOut.close();
                dbrsIn.clear();
                dbrsOut.clear();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }
        System.out.println(f1.inMoney+"-----------------------------");
        System.out.println(f1.outMoney+"-----------------------------");
        System.out.println(f1.nowMoney+"-----------------------------");
        return f1;
    }
    
    /**查询所有金额 (直接查询)
     * @throws Exception */
    public FirstForm selectMoneyForAll(DBConnection con,InOutStatisticSearchEvent event,String isFirstVisit,String warehouseCodes) throws Exception{

        String provider_name = event.getProviderName();
        String start = event.getStartStatisticTime();
        String end = event.getEndStatisticTime();
        String balance_mode = event.getBalanceMode();
        String balance_flag = event.getBalanceFlag();
        String concentricity_code = event.getProductCode();
        String productName=event.getProductName();
        String productBrand=event.getProductBrand();
        String is_normal = event.getIsNormal();
        String in = "";
        String out = "";
        
        in = " select "+
        " sum(insub.product_money) in_moneys "+  
        " from "+  
        " t_in_sub insub "+  
        " left outer join   t_in_head inhead "+   
        " on   insub.in_bill_id = inhead.id "+  
        " left outer join   t_product pro "+   
        " on   insub.product_id = pro.id "+  
        " left outer join   t_provider per "+   
        " on   per.code = inhead.provider_code "+  
        " where "+  
        " 1 = 1 and insub.status = '1'"+  
            " and  "+
            " inhead.warehouse_code  in ("+warehouseCodes+") ";
        if(provider_name != "")
            in += " and inhead.provider_code in (SELECT code  FROM t_provider WHERE NAME LIKE '%"+provider_name+"%' )"; 

        if(start != "")
            in += " and inhead.in_time >= '"+start+"' ";
        if(end != ""){
            if(end.length() <= 10) end += " 23:59:59";
            in += " and inhead.in_time <= '"+end+"' ";
        }    
        if(balance_mode != "")
            in += " and insub.balance_mode = '"+balance_mode+"' ";
        if(balance_flag != "")
            in += " and inhead.balance_flag = '"+balance_flag+"' ";
        if(concentricity_code != "")
            in += " and pro.concentricity_code = '"+concentricity_code+"' ";
    	if(productName != "")
     		in += " and pro.name like '%"+productName+"%' ";
     	if(productBrand != "")
     		in += " and pro.product_brand like '%"+productBrand+"%' ";
        if(is_normal != "")
            in += " and pro.is_normal = '"+is_normal+"' ";
    out = " select "+  
    " sum(outsub.product_money) out_moneys "+  
    " from  t_out_sub outsub "+  
    " left outer join   t_out_head outhead "+   
    " on   outsub.out_bill_code = outhead.out_bill_code "+  
    " left outer join   t_product pro "+   
    " on   outsub.product_id = pro.id "+  
    " left outer join   t_provider per "+   
    " on   per.code = pro.provider_code "+  
    " where "+  
    " 1 = 1 and outsub.status = '1'"+  
        " and  "+
        " outhead.warehouse_code  in ("+warehouseCodes+") ";
    if(provider_name != "")
    	out += " and outsub.provider_code in (SELECT code  FROM t_provider WHERE NAME LIKE '%"+provider_name+"%' )"; 
    if(start != "")
        out += " and outhead.out_time >= '"+start+"' ";
    if(end != ""){
        if(end.length() <= 10) end += " 23:59:59";
        out += " and outhead.out_time <= '"+end+"' ";
    }    
    if(balance_mode != "")
        out += " and outsub.balance_mode = '"+balance_mode+"' ";
    if(balance_flag != "")
        out += " and outhead.balance_flag = '"+balance_flag+"' ";
    if(concentricity_code != "")
        out += " and pro.concentricity_code = '"+concentricity_code+"' ";
	if(productName != "")
 		out += " and pro.name like '%"+productName+"%' ";
 	if(productBrand != "")
 		out += " and pro.product_brand like '%"+productBrand+"%' ";
    if(is_normal != "")
        out += " and pro.is_normal = '"+is_normal+"' ";
        
        DBResult dbrsIn = null;
        DBResult dbrsOut = null;
        ResultSet rowIn = null;
        ResultSet rowOut = null;
        FirstForm f1 = new FirstForm();
        try {
            dbrsIn = DBEngine.doSelect(con, in);
            dbrsOut = DBEngine.doSelect(con, out);
            rowIn = dbrsIn.getRs();
            rowOut = dbrsOut.getRs();
            if (rowIn.next()) {
                String str1 = rowIn.getString(1);
                if(str1 != null && !str1.equals("")){
                    f1.inMoney = Double.parseDouble(str1);
                }
            }
            if (rowOut.next()) {
                String str2 = rowOut.getString(1);
                if(str2 != null && !str2.equals("")){
                    f1.outMoney = Double.parseDouble(str2);
                }
            }
            f1.nowMoney = f1.inMoney - f1.outMoney;
        } catch (SQLException e) {
            
            e.printStackTrace();
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                rowIn.close();
                rowOut.close();
                dbrsIn.clear();
                dbrsOut.clear();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }
        System.out.println(f1.inMoney+"-----------------------------");
        System.out.println(f1.outMoney+"-----------------------------");
        System.out.println(f1.nowMoney+"-----------------------------");
        return f1;
    }
    
    /** 特定条件查询入库数量*/
    public Vector getInSum(DBConnection con,Vector mReturn,InOutStatisticSearchEvent event){
        Vector vec = mReturn;
        Vector v2 = new Vector();
        for (int i = 0; i < vec.size(); i++) {
            FirstForm f1 = (FirstForm)vec.get(i);
            DBResult dbrs = null;
            ResultSet rs = null;  
            String in = "select sum(b.PRODUCT_COUNT) " +
            		"from t_in_head a,t_in_sub b,t_product e,t_provider f " +
            		"where a.IN_BILL_CODE=b.IN_BILL_CODE and b.status = '1'" +
            		"and upper(b.PRODUCT_CODE) = e.CONCENTRICITY_CODE " +
            		"and a.PROVIDER_CODE = f.CODE " +
            		"and a.warehouse_code = '"+f1._warehouse_code+"' " +
            				"and b.POSITION_CODE = '"+f1._position_code+"' " +
            						"and upper(b.PRODUCT_CODE) = upper('"+f1._product_code+"') " +
            								"and b.PRODUCT_PRICE = '"+f1._product_price+"'";
            
            if (!event.getProviderName().equalsIgnoreCase("")) {
                in += " and f.name = '" + event.getProviderName() + "'";
            }
            if (!event.getBalanceFlag().equalsIgnoreCase("")) {
                in += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
            }
            if (!event.getStartStatisticTime().equalsIgnoreCase("")) {
                in += " and a.in_time >= '" + event.getStartStatisticTime() + " 00:00:00'";
            }
            if (!event.getEndStatisticTime().equalsIgnoreCase("")) {
                in += " and a.in_time <= '" + event.getEndStatisticTime() + " 23:59:59'";
            }
            if (!event.getIsNormal().equalsIgnoreCase("")) {
                in += " and e.is_normal = '" + event.getIsNormal() + "'";
            }
            if (!event.getBalanceMode().equalsIgnoreCase("")) {
                in += " and b.balance_mode = '" + event.getBalanceMode() + "'";
            }
            if (event.getWarehouseCodeUser().length() > 0) {
                in += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
            }
            if(event.getProductCode().length() > 0){
                in += " and upper(b.PRODUCT_CODE) like '%"+event.getProductCode().toUpperCase()+"%'";
            }
            try {
                dbrs = DBEngine.doSelect(con, in);
                rs = dbrs.getRs();
                if (rs.next()) {
                    f1.chanpinbianma = f1._product_code;
                    f1.rukudanjia = f1._product_price;
                    f1.chukudanjia = f1._product_price;
                    f1.rukushulaing = rs.getString(1);
                }
            } catch (SQLException e) {
                
                e.printStackTrace();
            } catch (Exception e) {
                
                e.printStackTrace();
            } finally {
                try {
                    dbrs.clear();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
            v2.add(f1);
        }
        return v2;
    }
    
    /** */
    public void inSumOper(){
        
    }
    /**内部formbean类*/
    public class FirstForm {
        //benny 查询使用属性
        public String billCode = "-0", warehouseName = "-0", positionName = "-0", productCode = "-0", productName = "-0", count = "-0", price = "-0";
        //teached 查询使用属性
        public String _warehouse_code = "-0",_position_code = "-0",_product_code = "-0",_product_price = "-0";
        public boolean small = true, big = true;
        //完整信息表达使用属性
        public String cangku = "-0", huowei = "-0", chanpinbianma = "-0", chanpinmingcheng = "-0", danwei = "-0", guige = "-0", xinghao = "-0", pinpai = "-0",
                gongyingshang = "-0", rukushulaing = "-0", rukujine = "-0", chukushuliang = "-0", chukuchengben = "-0", kucunshuliang = "-0",
                kucunchengben = "-0", rukudanjia = "-0", chukudanjia = "-0";
        public double  inMoney = 0,outMoney = 0,nowMoney = 0;
    }
}








































